package cn.melon.mod.library;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cn.melon.Helper.DBHelper;
import cn.melon.base.IBaseExtractor;
import net.ucanaccess.jdbc.UcanaccessSQLException;

public class LibraryBooksDaoImpl implements ILibraryBooksDao {
    
    private String sqlCreate = "CREATE TABLE librarybooks"
            + "(book_name TEXT(20), "
            + "book_id  TEXT(20),"
            + "publisher TEXT(20),"
            + "author TEXT(20),"
            + "loanable TEXt(1),"
            + "borrow_student TEXT(20),"
            + "borrow_date TEXt(20),"
            + "ddl TEXT(20),"
            + "return_date TEXT(20))";
            
    public DBHelper<LibraryBooksInfo> dbHelper;
    
    private IBaseExtractor<LibraryBooksInfo> extrcator = new IBaseExtractor<LibraryBooksInfo>() {

        @Override
        public List<LibraryBooksInfo> convertToList(ResultSet rs) {
            
            if (rs == null) 
                return null;
            
            List<LibraryBooksInfo> infoList = new ArrayList<>();
            try {
                while (rs.next()) {
                    LibraryBooksInfo info = new LibraryBooksInfo();
                    info.setBookName(rs.getString("book_name"));
                    info.setBookID(rs.getString("book_id"));
                    info.setPublisher(rs.getString("publisher"));
                    info.setAuthor(rs.getString("author"));
                    info.setLoanable(rs.getString("loanable"));
                    info.setBorrowStudent(rs.getString("borrow_student"));
                    info.setBorrowDate(rs.getString("borrow_date"));
                    info.setDDL(rs.getString("ddl"));
                    info.setReturnDate(rs.getString("return_date"));
                    infoList.add(info);
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
            
            return infoList;
        }
    };
    
    public LibraryBooksDaoImpl() {
        dbHelper = new DBHelper<>();
    }

    @Override
    public Boolean addBook(LibraryBooksInfo booksInfo) {
        String insertSql = "INSERT INTO librarybooks "
                + "(book_name, book_id, publisher, author, loanable, borrow_student, borrow_date, ddl, return_date) "
                + "VALUES "
                + "(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        String[] paras = new String[9];
        paras[0] = booksInfo.getBookName();
        paras[1] = booksInfo.getBookID();
        paras[2] = booksInfo.getPublisher();
        paras[3] = booksInfo.getAuthor();
        paras[4] = booksInfo.getLoanable();
        paras[5] = booksInfo.getBorrowStudent();
        paras[6] = booksInfo.getBorrowDate();
        paras[7] = booksInfo.getDDL();
        paras[8] = booksInfo.getReturnDate();

        return dbHelper.sqlUpdate(insertSql, paras);
    }

    @Override   
    public Boolean renewBook(LibraryBooksInfo booksInfo, String studentNum) {
        // 只是简易实现
        String sqlUpdate = "UPDATE librarybooks SET ddl = ? WHERE book_id = ?";
        String sqlQuery = "SELECT * FROM librarybooks WHERE book_id = ?";
        
        List<LibraryBooksInfo> infoList = null;
        try {
            infoList = dbHelper.
                            sqlQuery(sqlQuery, new String[]{booksInfo.getBookID()}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        if (infoList == null || infoList.size() == 0) {     // 未查到借阅信息
            return false;
        }
        
        String date[] = infoList.get(0).getDDL().split("-");
        
        Integer newMonth = Integer.valueOf(date[1]) + 1;
        Integer newYear = newMonth > 12? Integer.valueOf(date[0]) + 1 : Integer.valueOf(date[0]);
        newMonth = newMonth > 12 ? newMonth % 12: newMonth;

        date[0] = String.valueOf(newYear);
        date[1] = String.valueOf(newMonth);
        
        String newDate = date[0] + "-" + date[1] + "-" + date[2];
        
        return dbHelper.sqlUpdate(sqlUpdate, new String[]{newDate, booksInfo.getBookID()});
    }

    @Override
    public List<LibraryBooksInfo> getBorrowdBooks(String studentNum) {
        String sqlQuery = "SELECT * FROM librarybooks WHERE borrow_student = ?";
        try {
            return dbHelper.sqlQuery(sqlQuery, new String[]{studentNum}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        return null;
    }

    @Override
    public List<LibraryBooksInfo> getHotBooks() {
        // 只是简易实现返回所有信息
        String sqlQuery = "SELECT * FROM librarybooks";

        try {
            return dbHelper.sqlQuery(sqlQuery, new String[]{}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        return null;
    }
    
    public void onCreate() {
        try {
            dbHelper.sqlExecute(sqlCreate, new String[]{});
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
    }
    
    @Override
    public Boolean returnBooks(LibraryBooksInfo booksInfo, String studentNum) {

        String sqlUpdate = "UPDATE librarybooks set loanable = ? where book_id = ?";
        return dbHelper.sqlUpdate(sqlUpdate, new String[]{"0", booksInfo.getBookID()});
    }


    @Override
    public Boolean borrowBooks(LibraryBooksInfo booksInfo, String studentNum) {
        
        String sqlQuery = "SELECT * FROM librarybooks WHERE book_id = ?";
        
        List<LibraryBooksInfo> infoList = null;
        try {
             infoList = dbHelper.sqlQuery(sqlQuery, new String[] {booksInfo.getBookID()}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        if (infoList == null) {     // 未查到书籍
            return false;
        } 
        
        if (infoList.get(0).getLoanable().equals("0")) {        // 已经借出
            return false;
        }
        
        
        String sqlUpdate = "UPDATE librarybooks set loanable = ?, borrow_student = ?, borrow_date = ?, ddl = ? "
                + "where book_id = ?";
        
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String borrowDate = df.format(new Date());
        
        String date[] = borrowDate.split("-");
        
        Integer newMonth = Integer.valueOf(date[1]) + 1;
        Integer newYear = newMonth > 12? Integer.valueOf(date[0]) + 1 : Integer.valueOf(date[0]);
        newMonth = newMonth > 12 ? newMonth % 12: newMonth;

        date[0] = String.valueOf(newYear);
        date[1] = String.valueOf(newMonth);
        
        String ddlDate = date[0] + "-" + date[1] + "-" + date[2];
        
        return dbHelper.sqlUpdate(sqlUpdate, new String[]{"0", studentNum, borrowDate, ddlDate, booksInfo.getBookID()});
    }
    
    @Override
    public int getBorrowCount(String studentNum) {
        String sqlQuery = "SELECT * FROM librarybooks WHERE borrow_student = ?";
        List<LibraryBooksInfo> infoList = null;
        try {
            infoList = dbHelper.sqlQuery(sqlQuery, new String[]{studentNum}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        if (infoList == null) {
            return 0;
        } else {
            return infoList.size();
        }
    }


    @Override
    public Boolean updateBooks(LibraryBooksInfo booksInfo) {
        
        String insertSql = "UPDATE librarybooks SET "
                + "book_name = ?, publisher=?, "
                + "author = ?, loanable = ?, "
                + "borrow_student = ? , borrow_date = ?, "
                + "ddl = ?, return_date = ?"
                + "WHERE book_id = ?";
        
        String[] paras = new String[9];
        paras[0] = booksInfo.getBookName();
        paras[1] = booksInfo.getPublisher();
        paras[2] = booksInfo.getAuthor();
        paras[3] = booksInfo.getLoanable();
        paras[4] = booksInfo.getBorrowStudent();
        paras[5] = booksInfo.getBorrowDate();
        paras[6] = booksInfo.getDDL();
        paras[7] = booksInfo.getReturnDate();
        paras[8] = booksInfo.getBookID();

        return dbHelper.sqlUpdate(insertSql, paras);
    }


    @Override
    public List<LibraryBooksInfo> getAllBooks() {
        // 只是简易实现返回所有信息
        String sqlQuery = "SELECT * FROM librarybooks";

        try {
            return dbHelper.sqlQuery(sqlQuery, new String[]{}, extrcator);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
        
        return null;
    }

    @Override
    public Books getInfoBySomeThing(String bookID, String bookName) {
        // TODO Auto-generated method stub
        return null;
    }
}
